const router = require('koa-router')()
const sqlQuery = require('../db')
router.prefix('/admin')
router.get('/add', async (ctx, next) => {
    let sql = 'insert into admin_menu(role_id,menu_id) values(?,?)';
    for (let j = 1; j <= 5; j++) {
        for (let i = 1; i <= 10; i++) {
            await sqlQuery(sql, [j, i]);
        }
    }
    ctx.body = "成功"
})
// 查询所有用户
router.get('/admin', async (ctx, next) => {
    try {
        let { currentsize, pagesize } = ctx.query;
        let indexsize = (currentsize - 1) * pagesize;
        pagesize = parseInt(pagesize);
        let sql = `SELECT admins.id,admins.nickname,admins.age,(case when admins.gender=1 then '男' else '女' end) gender,admins.phone,admins.idCard,
        DATE_FORMAT(admins.createtime,'%Y-%m-%d %H:%i') datetime,(case when admin_accounts.state=1 then '正常' else '异常' end) state,
        admin_accounts.password,admin_accounts.logintime,admin_accounts.loginaddress,roles.role_name,headerimage.content
        FROM admins JOIN admin_accounts ON admins.id=admin_accounts.id AND admin_accounts.state=1 AND admins.state=1
        JOIN roles ON admins.role_id=roles.id AND roles.state=1
        JOIN headerimage ON admins.headerImage_id=headerimage.id AND headerimage.state=1 LIMIT ?,?`;
        let result = await sqlQuery(sql, [indexsize, pagesize]);
        let totalPage = await sqlQuery('select count(*) totalPage from admins', [])
        totalPage = totalPage[0].totalPage
        ctx.body = {
            status: 1,
            message: result,
            totalPage
        }
    } catch (error) {
        next(error)
    }
})
// 登录
router.post('/login', async (ctx, next) => {
    try {
        let { account, pwd } = ctx.request.body;
        console.log(account, pwd);
        let sql = `SELECT admins.id,admins.nickname,admins.age,admins.gender,admins.phone,admins.idCard,admins.createtime,
                    admin_accounts.password,admin_accounts.logintime,admin_accounts.loginaddress,roles.role_name,admins.role_id,headerimage.content
                    FROM admins JOIN admin_accounts ON admins.id=admin_accounts.id AND admin_accounts.state=1 AND admins.state=1 AND
                    admin_accounts.id=? AND admin_accounts.password=? JOIN roles ON admins.role_id=roles.id AND roles.state=1
                    JOIN headerimage ON admins.headerImage_id=headerimage.id AND headerimage.state=1`;
        let result = await sqlQuery(sql, [account, pwd]);
        ctx.body = {
            state: 200,
            message: result
        }
    } catch (error) {
        next(error)
    }
})
// 获取菜单
router.get('/getmenus/:role_id', async (ctx, next) => {
    try {
        let id = ctx.params.role_id;
        let sql = `SELECT m.id,m.title,m.url,m.iconfont icon,am.role_id FROM menus m 
        JOIN admin_menu am ON m.id=am.menu_id AND m.state=1 AND am.state=1 AND m.pid=0 HAVING am.role_id=?`;
        let result = await sqlQuery(sql, [id]);
        await Promise.all(result.map(async item => {
            item.id = item.id.toString();
            if (parseInt(item.url)) {
                let sql = `SELECT m.id,m.title,m.url,am.role_id  FROM menus m 
                JOIN admin_menu am ON m.id=am.menu_id AND m.state=1 AND am.state=1 AND m.pid=? AND am.role_id=?`;
                item.subs = await sqlQuery(sql, [item.id, id]);
            }
        }))
        ctx.body = {
            status: 200,
            message: result
        }
    } catch (error) {
        next(error)
    }
})

// 查询视频
router.get('/findLimitVideos', async (ctx, next) => {
    try {
        let { indexPage, pageSize } = ctx.query;
        pageSize = parseInt(pageSize);
        let indexsize = (indexPage - 1) * pageSize;
        let sql = `SELECT happy_video.id,happy_video.title,happy_video.url,
                    leftmenu.name fmenu,rightcontent.name smenu,(case when happy_video.state=1 then '正常' else '异常' end) state
                    FROM happy_video JOIN rightcontent ON happy_video.class_id=rightcontent.id
                    JOIN leftmenu ON rightcontent.pid=leftmenu.id LIMIT ?,?`;
        let all = await sqlQuery(sql, [indexsize, pageSize]);
        sql = `SELECT happy_video.id,happy_video.title,happy_video.url,
        leftmenu.name fmenu,rightcontent.name smenu,(case when happy_video.state=1 then '正常' else '异常' end) state
        FROM happy_video JOIN rightcontent ON happy_video.class_id=rightcontent.id
        JOIN leftmenu ON rightcontent.pid=leftmenu.id AND happy_video.state=1 LIMIT ?,?`;
        let normal = await sqlQuery(sql, [indexsize, pageSize]);
        sql = `SELECT happy_video.id,happy_video.title,happy_video.url,
                leftmenu.name fmenu,rightcontent.name smenu,(case when happy_video.state=1 then '正常' else '异常' end) state
                FROM happy_video JOIN rightcontent ON happy_video.class_id=rightcontent.id
                JOIN leftmenu ON rightcontent.pid=leftmenu.id AND happy_video.state=0 LIMIT ?,?`;
        let unnormal = await sqlQuery(sql, [indexsize, pageSize]);
        let totalPage = await sqlQuery('select count(*) totalPage from happy_video', []);
        totalPage = totalPage[0].totalPage
        let normalTotalPage = await sqlQuery('select count(*) totalPage from happy_video WHERE state=1', []);
        normalTotalPage = normalTotalPage[0].totalPage
        let unnormalTotalPage = await sqlQuery('select count(*) totalPage from happy_video WHERE state=0', []);
        unnormalTotalPage = unnormalTotalPage[0].totalPage
        ctx.body = {
            message: {
                all,
                normal,
                normalTotalPage,
                unnormal,
                unnormalTotalPage
            },
            totalPage
        };
    } catch (error) {
        next(error)
    }
})

// 修改视频状态
router.post('/updateVideoState', async (ctx, next) => {
    try {
        let { id, state } = ctx.request.body;
        let sql = `UPDATE happy_video SET state=? WHERE id=?`;
        let result = await sqlQuery(sql, [state, id]);
        ctx.body = {
            state: 200,
            message: result
        }
    } catch (error) {
        next(error)
    }
})

// 查询所有用户
router.get('/users', async (ctx, next) => {
    try {
        let { currentsize, pagesize } = ctx.query;
        let indexsize = (currentsize - 1) * pagesize;
        pagesize = parseInt(pagesize);
        let sql = `SELECT id,code,avatarUrl,nickName,city,province,country,(case when gender=1 then '男' else '女' end) gender,
        (case when state=1 then '正常' else '异常' end) state FROM users LIMIT ?,?`;
        let result = await sqlQuery(sql, [indexsize, pagesize]);
        let totalPage = await sqlQuery('select count(*) totalPage from users', [])
        totalPage = totalPage[0].totalPage
        ctx.body = {
            status: 1,
            message: result,
            totalPage
        }
    } catch (error) {
        next(error)
    }
})
// 查询视频通过id
router.get('/getVideoById', async (ctx, next) => {
    try {
        let { vid } = ctx.query;
        let sql = `SELECT id,title,url,class_id FROM happy_video WHERE id=?`;
        let video = await sqlQuery(sql, [vid]);
        sql = `SELECT id,name FROM rightcontent`;
        let classify = await sqlQuery(sql, []);
        ctx.body = {
            video,
            classify
        }
    } catch (error) {
        next(error)
    }
})

// 修改视频
router.post('/updtaeVideo', async (ctx, next) => {
    try {
        let { id, title, url, class_id } = ctx.request.body;
        let sql = `UPDATE happy_video SET title=?,url=?,class_id=? WHERE id=?`;
        let result = await sqlQuery(sql, [title, url, class_id, id]);
        ctx.body = {
            state: 200,
            message: result
        }
    } catch (error) {
        next(error)
    }
})
module.exports = router